﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace TCPIPControl
{
 
    public  class MyDB_VB : IDisposable
    {
        /// <summary></summary>
        private enum SqlType : int
        {
            /// <summary>Access</summary>
            Access = 0,

            /// <summary>SQL Server</summary>
            SqlServer
        }

        #region 

        /// <summary</summary>
        private SqlType _type;

        /// <summary></summary>
        private SqlConnection _connection = null;

        /// <summary></summary>
        private SqlCommand _command = null;

        /// <summary></summary>
        private SqlDataReader _reader = null;

        /// <summary></summary>
        private List<SqlParameter> _params = new List<SqlParameter>();

        public string dbconnectionstring = "";
        #endregion

        #region 

        /// <summary></summary>
        public SqlDataReader DataReader
        {
            get { return _reader; }
        }

        #endregion

        #region 

        /// <summary></summary>
        public MyDB_VB(string conn)
        {
            dbconnectionstring = conn;
        }

        #endregion

        #region 

        /// <summary></summary>
        public void Dispose()
        {
            Close();
        }

        /// <summary></summary>
        /// <returns></returns>
        public bool Open()
        {
            //SystemINI sysini = SystemINI.ReadINIFile();
            //Cls_SystemSet ss = new Cls_SystemSet();

            //string dbconnectionstring = Mdl_Class.Systemset.DBConnectionString();

            return Open(dbconnectionstring);
        }

        /// <summary>打开连接</summary>
        /// <param _name="connectionString"></param>
        /// <returns></returns>
        public bool Open(string connectionString)
        {
            //
            _type = (connectionString.Contains(".mdb")) ? SqlType.Access : SqlType.SqlServer;

            if (_reader != null)
                _reader.Dispose();

            if (_command != null)
                _command.Dispose();

            if (_connection != null)
                _connection.Dispose();

            _params.Clear();

            switch (_type)
            {
                //case DbType.Access:
                //    _connection = new OleDbConnection();
                //    _command = new OleDbCommand();
                //    break;

                case SqlType.SqlServer:
                    _connection = new SqlConnection();
                    _command = new SqlCommand();
                    break;
            }

          
            if (_connection.State == System.Data.ConnectionState.Open)
                return true;

            try
            {
                _connection.ConnectionString = connectionString;
                _connection.Open();
                _command.Connection = _connection;
            }
            catch (Exception ex)
            {
                //MessageBox.Show("数据库连接失败！", "提示");
                //Mdl_Class.WriteLog.dbErrLog("数据库连接失败!", ex.ToString());
                return false;
            }

            return true;
        }

        /// <summary>关闭连接</summary>
        public void Close()
        {
            if (_reader != null)
                _reader.Dispose();

            if(_command!=null)
            _command.Dispose();

            if (_connection != null)
            _connection.Dispose();
            return;
        }

        /// <summary>SELECT查询</summary>
        /// <param name="sql">SQL</param>
        /// <returns>true或者false</returns>
        public bool ExecuteReader(string sql)
        {
            try
            {
                if (_reader != null)
                    _reader.Dispose();

                _command.Parameters.Clear();
                if (_params != null && _params.Count != 0)
                    _command.Parameters.AddRange(_params.ToArray());

                _command.CommandText = sql;

                _reader = _command.ExecuteReader();
            }
            catch (Exception)
            {

                return false;
            }

            return true;
        }

        /// <summary>SELECT查询</summary>
        /// <param name="sql">SQL</param>
        /// <returns>返回一个datatable</returns>
        public DataTable DataTableExecuteReader(string sql)
        {
            DataTable dt = null;
            try
            {
                if (_reader != null)
                    _reader.Dispose();

                _command.Parameters.Clear();
                if (_params != null && _params.Count != 0)
                    _command.Parameters.AddRange(_params.ToArray());

                _command.CommandText = sql;
                DataSet ds = new DataSet();
                SqlDataAdapter sda = new SqlDataAdapter(_command);
                sda.Fill(ds);
                dt = ds.Tables[0];
                //return dt;
                //_reader = _command.ExecuteReader();
                //_
            }
            catch (Exception)
            {

                return null;
            }

            return dt;
        }

        /// <summary>INSERT丄UPDATE丄DELETE语句</summary>
        /// <param name="sql">sql</param>
        /// <returns>int</returns>
        public int ExecuteNonQuery(string sql)
        {
            int result;

            try
            {
                _command.CommandText = sql;

                _command.Parameters.Clear();
                if (_params != null && _params.Count != 0)
                    _command.Parameters.AddRange(_params.ToArray());

                result = _command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                result = -1;
            }

            return result;
        }

        public string ExecuteScalar(string sql)
        {
            string result;

            try
            {
                _command.CommandText = sql;

                _command.Parameters.Clear();
                if (_params != null && _params.Count != 0)
                    _command.Parameters.AddRange(_params.ToArray());

                result = Convert.ToString(_command.ExecuteScalar());
            }
            catch (Exception)
            {
                result = "-1";
            }

            return result;
        }


            
        public string ExecuteScalar(string sql,byte[] by1)
        {
            string result;

            try
            {
                _command.CommandText = sql;

                _command.Parameters.Clear();
                _command.Parameters.Add("@abc", SqlDbType.Image).Value = by1;

                if (_params != null && _params.Count != 0)
                    _command.Parameters.AddRange(_params.ToArray());

                result = Convert.ToString(_command.ExecuteScalar());
            }
            catch (Exception)
            {
                result = "-1";
            }

            return result;
        }
        public int ExecuteNonQuery1(string sql,byte[] by1)
        {
            int result;

            try
            {
                //_command.CommandText = string.Format("insert into T_B_Test(UnitID,InsertTime,ChannelData) values('{0}','{1}',@abc)",1,DateTime.Now);

                _command.Parameters.Clear();
                _command.Parameters.Add("@abc", SqlDbType.Image).Value = by1;
                _command.CommandText = sql;
                //if (_params != null && _params.Count != 0)
                //    _command.Parameters.AddRange(_params.ToArray());

                result = _command.ExecuteNonQuery();
                //_command.CommandText = string.Format("insert into T_B_Test2(UnitID,InsertTime,ChannelData) values('{0}','{1}',@abc)", 1, DateTime.Now);

                //result = _command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                result = -1;
            }

            return result;
        }







        /// <summary>开始事务</summary>
        /// <remarks>
        /// </remarks>
        public void BeginTransaction()
        {
            _command.Transaction = _connection.BeginTransaction();
        }

        /// <summary>提交事务</summary>
        public void Commit()
        {
            _command.Transaction.Commit();
        }

        /// <summary>回滚事务</summary>
        public void Rollback()
        {
            _command.Transaction.Rollback();
        }

        /// <summary>清除</summary>
        public void ClearParam()
        {
            _params.Clear();
        }

        /// <summary>sql或者access</summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <remarks>

        /// </remarks>
        public void AddParam(string key, object value)
        {
            SqlParameter param = null;

            switch (_type)
            {
                //case DbType.Access:
                //    param = new OleDbParameter(key, value);
                //    break;

                case SqlType.SqlServer:
                    param = new SqlParameter(key, value);
                    break;
            }

            _params.Add(param);
        }

        #endregion
    }
}
